<?php

class Tasks_model extends Model
{
	
	private $enckey = null;
	
	function Tasks_model()
	{
		parent::Model();
		$this->enckey = TASKS_KEY;
	}
	
	
	public function insert($user_id, $content, $tag_id)
	{
		# create MONTHLY tables for tasks, because one table for tasks can get HUGE very fast
		$tag_id = (int)$tag_id;
		$content = $this->db->escape($content);
		$dt = local_to_gmt(time()); # tasks datetime is always stored as GMT datetime
		$sql = "INSERT INTO enctask 
				SET user_id = $user_id,
					content = AES_ENCRYPT($content, '$this->enckey'),
					state = 0,
					date_created = $dt";
		$this->db->query($sql);
		
		if($this->db->affected_rows() == 0) return FALSE;
		
		$new_task_id = $this->db->insert_id();
		
		if($tag_id > 0)
		{
			$this->db->query("INSERT INTO task_tag SET task_id = $new_task_id, tag_id = $tag_id");
		}
		
		return $new_task_id;
	}
	
	
	public function getTasks($user_id, $tag_id = 0, $start = 0, $limit = MAX_TASKS_VISIBLE_ON_START)
	{
		$tag_id = (int)$tag_id;
		
		if($tag_id > 0)
		{
			$sql = "SELECT 
						t.task_id, 
						t.user_id, 
						t.content, 
						t.date_created AS date_created, 
						t.duedate, 
						t.priority, 
						t.state, 
						GROUP_CONCAT(tg.name) AS tags
					FROM(
							SELECT enctask.task_id, 
								   enctask.user_id,
							   	   AES_DECRYPT(enctask.content, '$this->enckey') AS content,
							   	   enctask.date_created,
							   	   enctask.duedate,
							   	   enctask.priority,
							   	   enctask.state
							FROM enctask
							INNER JOIN task_tag ON task_tag.task_id = enctask.task_id
							WHERE enctask.user_id = $user_id AND task_tag.tag_id = $tag_id
							ORDER BY enctask.date_created DESC
							LIMIT $start, $limit
						) AS t
					LEFT JOIN task_tag tt ON tt.task_id = t.task_id
					LEFT JOIN tag tg ON tg.tag_id = tt.tag_id
					GROUP BY t.task_id
				 	ORDER BY t.date_created DESC";
				 	
					//WHERE t.task_id IN (SELECT task_id FROM task_tag WHERE tag_id = $tag_id)
		}
		else
		{
			$sql = "SELECT 
						t.task_id, 
						t.user_id, 
						t.content, 
						t.date_created AS date_created, 
						t.duedate, 
						t.priority, 
						t.state, 
						GROUP_CONCAT(tg.name) AS tags
					FROM(
							SELECT enctask.task_id, 
								   enctask.user_id,
							   	   AES_DECRYPT(enctask.content, '$this->enckey') AS content,
							   	   enctask.date_created,
							   	   enctask.duedate,
							   	   enctask.priority,
							   	   enctask.state
							FROM enctask
							WHERE enctask.user_id = $user_id
							ORDER BY enctask.date_created DESC
							LIMIT $start, $limit
						) AS t
					LEFT JOIN task_tag tt ON tt.task_id = t.task_id
					LEFT JOIN tag tg ON tg.tag_id = tt.tag_id
					GROUP BY t.task_id
				 	ORDER BY t.date_created DESC";
		}
		
		log_message('debug', $sql);
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	public function getTask($user_id, $task_id)
	{
		$sql = "SELECT t.task_id, AES_DECRYPT(t.content, '$this->enckey') AS content, 
					   t.date_created, t.state, t.duedate, t.priority, GROUP_CONCAT(tg.name) AS tags
				FROM enctask t
				LEFT JOIN task_tag tt ON tt.task_id = t.task_id
				LEFT JOIN tag tg ON tg.tag_id = tt.tag_id
				WHERE t.user_id = $user_id AND t.task_id = $task_id
				GROUP BY t.task_id";
		log_message('debug', $sql);
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	public function tasksCount($user_id, $tag_id)
	{
		$tag_id = (int)$tag_id;
		if($tag_id > 0)
		{
			$sql = "SELECT count(enctask.task_id) AS tasks_count 
					FROM enctask 
					INNER JOIN task_tag ON task_tag.task_id = enctask.task_id
					WHERE enctask.user_id = $user_id AND task_tag.tag_id = $tag_id";	
		}
		else 
		{
			$sql = "SELECT count(task_id) AS tasks_count 
					FROM enctask 
					WHERE user_id = $user_id";			
		}
		
		log_message('debug', $sql);
		$query = $this->db->query($sql);
		return $query->result();			
	}
	
	
	public function getTags($user_id)
	{
		$sql = "SELECT t.tag_id, t.name, count(tk.task_id) AS count
				FROM tag t
				INNER JOIN task_tag tt ON t.tag_id = tt.tag_id
				INNER JOIN enctask tk ON tk.task_id = tt.task_id
				WHERE user_id = $user_id
				GROUP BY t.tag_id";
		log_message('debug', $sql);
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	public function setState($user_id, $task_id, $state)
	{
		$sql = "UPDATE enctask SET state = $state WHERE task_id = $task_id AND user_id = $user_id";
		$this->db->query($sql);
		
		//if($this->db->_error_message()) return FALSE;
		return TRUE;
	}
	
	
	public function save($user_id, $task)
	{
		log_message('debug', 'save task: ' . var_export($task, 1));
		if((int)$user_id == 0 || (int)$task->id == 0) return false;
		
		/*
		# check if the task belongs to the user
		$sql = "SELECT task_id FROM enctask WHERE task_id = $task->id AND user_id = $user_id";
		$query = $this->db->query($sql);
		$result = $query->row();
		if((int)$result->task_id == 0) return FALSE;
		*/
		
		$content = $this->db->escape($task->content);
		$task->duedate = isset($task->duedate) ? (int)$task->duedate : 0;
		$sql = "UPDATE enctask
				SET content = AES_ENCRYPT($content, '$this->enckey'),
					duedate = IF($task->duedate > 0, FROM_UNIXTIME($task->duedate), NULL)
				WHERE task_id = $task->id AND user_id = $user_id";
		$this->db->query($sql);
		
		/*
		if(is_array($task->tags))
		{
			# format the string to be used in the insert query
			# example string elements of the array will be "('tagname')"
			$tags_formatted = array();
			foreach($task->tags as $tag)
			{
				$tags_formatted[] = '(' . $this->db->escape($tag) . ')';
			}

			# after the tags_formatted array is imploded the resulting string will be something like
			# ('sample_tag_1'),('sample_tag_2'),('...')
			# this is made in order to insert multiple tag rows with one query
			$tags_for_insert = implode(',', $tags_formatted);
			$sql = "INSERT IGNORE INTO tag (name) VALUES" . $tags_for_insert;
			log_message('debug', $sql);			
			$this->db->query($sql);
			
			
			# get the ids of all currently inserted tags in order to update the mapping table TASK_ID <-> TAG_ID
			# again we have to format the tags to use them in sql IN clause
			$tags_formatted = array();
			foreach($task->tags as $tag)
			{
				$tags_formatted[] = $this->db->escape($tag);
			}			
			$tags_for_select = implode(',', $tags_formatted);
			$sql = "SELECT tag_id FROM tag WHERE name IN ($tags_for_select)";			
			log_message('debug', $sql);
			$query = $this->db->query($sql);
			
			$tags_ids = array();
			foreach($query->result() as $row)
			{
				$tags_ids[] = $row->tag_id;
			}


			# first delete all the assosiations from the mapping table for this task
			# and then insert all tags again
			# this is done in order to utilize the case when there are tags deleted from a given article
			# if done the straight way, we have to first select current tags and then compare with new tags
			# then we have to delete these that don't match and insert the new ones;
			# it seems easier to just delete all tag's mapping and then insert them again
			$sql = "DELETE FROM task_tag WHERE task_id = $task->id";
			log_message('debug', $sql);
			$this->db->query($sql);
			
			# insert all tags in the mapping table
			$tags_for_insert = array();
			foreach($tags_ids as $tag_id)
			{
				$tags_for_insert[] = "(" . $task->id . ',' . $tag_id . ")"; 
			}
			
			$tags_for_insert = implode(',', $tags_for_insert);
			$sql = "INSERT INTO task_tag (task_id, tag_id) VALUES " . $tags_for_insert;
			log_message('debug', $sql);
			$this->db->query($sql);
		}
		elseif($task->tags !== NULL && $task->tags === '')
		{
			# delete all task tags if the user is sending empty task string
			$sql = "DELETE FROM task_tag WHERE task_id = $task->id";
			log_message('debug', $sql);
			$this->db->query($sql);
		}
		*/

		return TRUE;
	}
	
	
	public function saveTags($user_id, $task_id, $tags)
	{
		/*
		# check if the task belongs to the user
		$sql = "SELECT task_id FROM enctask WHERE task_id = $task_id AND user_id = $user_id";
		$query = $this->db->query($sql);
		$result = $query->row();
		if((int)$result->task_id == 0) return FALSE;
		*/
		
		if(is_array($tags))
		{
			# format the string to be used in the insert query
			# example string elements of the array will be "('tagname')"
			$tags_formatted = array();
			foreach($tags as $tag)
			{
				$tags_formatted[] = '(' . $this->db->escape($tag) . ')';
			}

			# after the tags_formatted array is imploded the resulting string will be something like
			# ('sample_tag_1'),('sample_tag_2'),('...')
			# this is made in order to insert multiple tag rows with one query
			$tags_for_insert = implode(',', $tags_formatted);
			$sql = "INSERT IGNORE INTO tag (name) VALUES" . $tags_for_insert;
			log_message('debug', $sql);			
			$this->db->query($sql);
			
			
			# get the ids of all currently inserted tags in order to update the mapping table TASK_ID <-> TAG_ID
			# again we have to format the tags to use them in sql IN clause
			$tags_formatted = array();
			foreach($tags as $tag)
			{
				$tags_formatted[] =  $this->db->escape($tag);
			}			
			$tags_for_select = implode(',', $tags_formatted);
			$sql = "SELECT tag_id FROM tag WHERE name IN ($tags_for_select)";			
			log_message('debug', $sql);
			$query = $this->db->query($sql);
			
			$tags_ids = array();
			foreach($query->result() as $row)
			{
				$tags_ids[] = $row->tag_id;
			}


			# first delete all the assosiations from the mapping table for this task
			# and then insert all tags again
			# this is done in order to utilize the case when there are tags deleted from a given article
			# if done the straight way, we have to first select current tags and then compare with new tags
			# then we have to delete these that don't match and insert the new ones;
			# it seems easier to just delete all tag's mapping and then insert them again
			$sql = "DELETE FROM task_tag WHERE task_id = $task_id";
			log_message('debug', $sql);
			$this->db->query($sql);
			
			# insert all tags in the mapping table
			$tags_for_insert = array();
			foreach($tags_ids as $tag_id)
			{
				$tags_for_insert[] = "(" . $task_id . ',' . $tag_id . ")"; 
			}
			
			$tags_for_insert = implode(',', $tags_for_insert);
			$sql = "INSERT INTO task_tag (task_id, tag_id) VALUES " . $tags_for_insert;
			log_message('debug', $sql);
			$this->db->query($sql);
		}
		elseif($tags !== NULL && $tags === '')
		{
			# delete all task tags if the user is sending empty task string
			$sql = "DELETE FROM task_tag WHERE task_id = $task_id";
			log_message('debug', $sql);
			$this->db->query($sql);
		}
		
		return true;
	}
	
	
	public function delete($user_id, $task_id)
	{
		$sql = "DELETE FROM enctask WHERE user_id = $user_id AND task_id = $task_id";
		log_message('debug', $sql);
		
		$this->db->query($sql);
		if($this->db->affected_rows() == 0) return FALSE;
		
		$sql = "DELETE FROM task_tag WHERE task_id = $task_id";
		$this->db->query($sql);
		if($this->db->_error_message()) return FALSE;
				
		return TRUE;
	}
	
	
	public function deleteMultiple($user_id, $taks_ids)
	{
		$sql = "DELETE FROM enctask WHERE user_id = $user_id AND task_id IN ($task_ids)";
		log_message('debug', $sql);
		
		$this->db->query($sql);
		if($this->db->affected_rows() == 0) return FALSE;
		
		$sql = "DELETE FROM task_tag WHERE task_id IN ($task_ids)";
		log_message('debug', $sql);
		
		$this->db->query($sql);
		if($this->db->_error_message()) return FALSE;
				
		return TRUE;		
	}


	public function priority($user_id, $task_id, $priority)
	{
		$sql = "UPDATE enctask SET priority = $priority WHERE task_id = $task_id AND user_id = $user_id";
		$this->db->query($sql);
		return TRUE;	
	}	


	public function setDuedate($user_id, $task_id, $duedate)
	{
		$sql = "UPDATE enctask SET duedate = $duedate WHERE task_id = $task_id AND user_id = $user_id";
		$this->db->query($sql);
		return TRUE;		
	}


	public function clearOrphanedTags()
	{
		$sql = 'DELETE tag FROM tag
				LEFT JOIN task_tag ON tag.tag_id = task_tag.tag_id
				WHERE task_tag.tag_id IS NULL';
		$this->db->query($sql);
	}
	
	
	public function removeUser($user_id)
	{
		# delete task <-> tag associations
		$sql = "DELETE FROM task_tag 
				WHERE task_tag.task_id IN (SELECT task_id FROM enctask WHERE user_id = $user_id)";
		$this->db->query($sql);					
		
		# delete tasks
		$sql = "DELETE FROM enctask WHERE user_id = $user_id";
		$this->db->query($sql);
		
		return TRUE;
	}
	

	//private $enckey = 's73kg9dk*%FW&72MV.<SLAo9d9!FDSx~'; # !!!!!!DO-NOT-MODIFY!!!!!
}